package logic;

import java.io.File;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

import oracle.jdbc.OracleTypes;
import utilities.database.SQLLoader;
import utilities.file.BinaryFileIn;
import utilities.file.CSVFileOut;

/**
 * @author Geoffrey Shaw and Kris Deviat
 * This class is the broker between our system and the SQL database. Provides connectivity, query and functionality related
 * to the SQL database
 *
 */
public class DatabaseBroker {
	private static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
	
	private Connection connection;
	private static DatabaseBroker db = null;
	
	private DatabaseBroker() {
		
	}
	
	/**
	 * @param username the username needed to connect to the database
	 * @param password the password needed to connect to the database
	 * @throws Exception Throws ClassNotFound, SQLException... all the exceptions
	 * This method is used to connect to the database
	 */
	public void connect(String username, String password) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		connection = DriverManager.getConnection(URL, username, password);
	}
	
	/**
	 * @return an instance of the database Broker
	 */
	public static DatabaseBroker getDatabaseBroker() {
		if(db == null) {
			db = new DatabaseBroker();
		}
		
		return db;
	}
	
	
	/**
	 * @return a boolean that checks if the payroll is locked
	 * @throws Exception An sqlexception, malformed statements and other goodies
	 * This method is used to check that the payroll can be loaded by calling the lock procedure
	 */
	public boolean CheckPayrollLoad() throws Exception {
		CallableStatement cstmt = connection.prepareCall("{? = call payroll_lock}");
		cstmt.registerOutParameter(1, OracleTypes.NUMBER);
		cstmt.execute();
		
		return cstmt.getBoolean(1);
	}
	
	/**
	 * @return a boolean stating whether the payroll is unlocked or not
	 * @throws Exception SQLException
	 * This method checks to see if the payroll is unlocked, and returns a boolean representing that
	 */
	public boolean CheckPayrollUnlock() throws Exception {
		CallableStatement cstmt = connection.prepareCall("{? = call payroll_unlock}");
		cstmt.registerOutParameter(1, OracleTypes.NUMBER);
		cstmt.execute();
		return cstmt.getBoolean(1);
	}
	
	/**
	 * @param csvFileName The name of the CSV File
	 * @param binaryFileName The name of the Binary File
	 * @throws Exception SQLException
	 * This method takes in the CSV File name and a Binary File name, then uses the CSV Method to generate a
	 * CSV based on the object
	 */
	public void CreateCVSFromBinary(String cvsFileName, String binaryFileName) throws Exception {
		BinaryFileIn b = new BinaryFileIn(binaryFileName);
		CSVFileOut csv = new CSVFileOut(cvsFileName);
		csv.setDelimiter(';');
		
		int loc = 1;
		PayrollRecord p = b.readBinaryFile(loc);
		
		while(p != null) {						
			csv.addElement(p);
			
			loc++;
			p = b.readBinaryFile(loc);
		}
		
		b.close();
		csv.close();
	}
	
	/**
	 * 
	 * @throws Exception SQL Exception
	 * Calls the month end procedure
	 */
	public void PerformMonthEnd() throws Exception {
		CallableStatement cstmt = connection.prepareCall("{call month_end_proc}");
		cstmt.execute();
	}
	
	/**
	 * @param csvFile The name of the CSV file being loaded to the database
	 * @param username the username of the person accessing the system
	 * @param password the password of the person accessing the system
	 * @throws Exception SQLException
	 * This method calls the SQLLoader class to get the .CTL file needed to load a CSV into a database
	 */
	public void loadCSVtoDatabase(String csvFile, String username, String password) throws Exception {
		File file = new File(csvFile);
		
		File parentFile = file.getParentFile();
		
		String rofl = parentFile.getAbsolutePath() + "\\log.txt";
		String fp = parentFile.getAbsolutePath() + "\\control.ctl";
		String kk = file.getAbsolutePath();
		
		SQLLoader s = new SQLLoader(fp, kk, ';');
		
		String cmd = "sqlldr userid=" + username + "/" + password + " control=" + fp + " log=" + rofl;
		
		Runtime rt = Runtime.getRuntime();
		Process proc = rt.exec(cmd);
		int exitValue = proc.waitFor();
	}
	/**
	 * 
	 * @return A boolean stating whether the month end is locked
	 * @throws Exception SQLException
	 * This method checks to see whether the month end procedure is locked
	 */
	public boolean CheckMonthEndLock() throws Exception {
		CallableStatement cstmt = connection.prepareCall("{? = call month_end_lock}");
		cstmt.registerOutParameter(1, OracleTypes.NUMBER);
		cstmt.execute();
		return cstmt.getBoolean(1);
	}
	
	/**
	 * @return A boolean cheacking if the month end process is unlocked
	 * @throws Exception SQLException
	 * This method checks to see if the month end is unlocked or not
	 */
	public boolean CheckMonthEndUnLock() throws Exception {
		CallableStatement cstmt = connection.prepareCall("{? = call month_end_unlock}");
		cstmt.registerOutParameter(1, OracleTypes.NUMBER);
		cstmt.execute();
		return cstmt.getBoolean(1);
	}
	
	/**
	 * @param alias The name of the alias to be used
	 * @throws Exception SQLException
	 * This method sets the Alias to be used when the user exports the data
	 */
	public void setAlias(String alias) throws Exception {
		String sql = "CREATE OR REPLACE DIRECTORY " + alias + " AS 'c:\\cprg300'";
		
		Statement stmt = connection.createStatement();
		stmt.executeUpdate(sql);
	}
	
	/**
	 * @param fileName The name of the file that is exported
	 * @param alias The alias of the filepath to be used
	 * @throws Exception SQLException
	 * This method calls the export data procedure, which will use the filepath alias and the filename to export the file
	 */
	public void exportNewTransactions(String fileName, String alias) throws Exception {
		setAlias(alias);
		
		CallableStatement cstmt = connection.prepareCall("{call export_data_proc(?, ?)}");
		cstmt.setString(1,alias.toUpperCase());
		cstmt.setString(2,fileName);
		cstmt.execute();
	}
}
